package org.anyline.simple.postgres;

import org.anyline.data.jdbc.adapter.JDBCAdapter;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.entity.*;
import org.anyline.metadata.*;
import org.anyline.metadata.Table.Partition;
import org.anyline.proxy.CacheProxy;
import org.anyline.service.AnylineService;
import org.anyline.util.*;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.*;

@SpringBootTest
public class PostgreTest {
    private Logger log = LoggerFactory.getLogger(PostgreTest.class);
    @Autowired
    private AnylineService service          ;
    @Autowired
    private JdbcTemplate jdbc               ;
    private Catalog catalog  = null          ; // 可以相当于数据库名
    private Schema schema   = null          ; // 如 dbo
    private String table    = "CRM_USERS"    ; // 表名

    @Test
    public void schemas() throws Exception {
        ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
        //LinkedHashMap<String, Catalog> catalogs = service.metadata().catalogs();
        //System.out.println(catalogs);
        LinkedHashMap<String, Schema> schemas = service.metadata().schemas(new Catalog("simple"));
        System.out.println(schemas);
        schemas = service.metadata().schemas();
        System.out.println(schemas);
    }
    @Test
    public void float_double() throws Exception{
        type("float");          //float        正常
        type("float(32)");      //float(32)    正常
        type("float(10)");      //float(10)    正常
        type("float(10,2)");    //float(10,2)  异常
        type("float4");          //float       异常
        type("float4(32)");      //float(32)   正常
        type("float4(10)");      //float(10)   异常
        type("float4(10,2)");    //float(10,2) 异常
        type("float8");          //float       异常
        type("float8(32)");      //float(32)   异常
        type("float8(10)");      //float(10)   异常
        type("float8(10,2)");    //float(10,2) 异常
        type("double");         //double       异常(会换成number)
        type("double(32)");     //double(32)   异常(会换成number)
        type("double(10)");     //double(10)   异常(会换成number)
        type("double(10,2)");   //double(10,2) 异常(会换成number)
    }
    private void type(String type){
        try {
            String sql = "CREATE TABLE TAB_" + System.currentTimeMillis() + "(id int, code "+type+")";
            service.execute(sql);
            System.out.println(LogUtil.format(type + " 正常", 32));
        }catch (Exception e){
            System.out.println(LogUtil.format(type + " 异常", 31));
        }
    }

    @Test
    public void pk2() throws Exception{
        Table tab = service.metadata().table("B_TEST", false);
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table("B_TEST");
        tab.addColumn("ID", "INT").autoIncrement(true).primary(true);
        tab.addColumn("CODE", "INT");
        service.ddl().save(tab);

        tab = new Table("B_TEST");
        tab.addColumn("ID", "INT").primary(false);
        tab.addColumn("CODE", "INT");
        tab.addColumn("IDS", "INT").primary(true).autoIncrement(true).setComment("新主键");
        tab.execute(false);
        service.ddl().save(tab);
        List<String> ddls = tab.ddls();
        for(String ddl:ddls){
            System.out.println(ddl);
        }
        List<Run> runs = tab.runs();
        for(Run run:runs){
            System.out.println(run.getFinalUpdate());
        }
    }
    @Test
    public void tabQuery() throws Exception{
        Table table = service.metadata().table("crm_user", false);
        if(null == table){
            table = new Table("crm_user");
            table.addColumn("ID","INT");
            service.ddl().create(table);
        }
        List<Table> tables = service.metadata().tables(false, new Catalog("SIMPLE"), new Schema(null), "crm_user",1, false);
        System.out.println(tables);
    }

    @Test
    public void dbl1() throws Exception {
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if (null != table) {
            service.ddl().drop(table);
        }
        //定义表结构
        table = new Table(catalog, schema, this.table);
        table.setComment("表备注");
        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("DATA_VERSION", "double(10,1)", false, 1.1).setComment("数据版本");
        table.addColumn("DATA_VERSION2", "double", false, 1.1).setPrecision(10).setScale(2);

        //创建表
        service.ddl().create(table);
        List<String> ddls = table.getDdls();
        for(String ddl:ddls){
            System.out.println("================\n"+ddl);
        }

    }
    @Test
    public void table_metadata() throws Exception{
        ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
        List<String> ddls = service.metadata().ddl(new Table("crm_user"));
        System.out.println(ddls);
    }
    @Test
    public void var(){
        String s = "SELECT \n" +
                "i.indkey AS COLUMN_POSITIONS,\n" +
                "i.indoption::varchar AS COLUMN_ORDERS\n" +
                "FROM pg_index AS i";
        ConfigStore configs = new DefaultConfigStore();
        configs.IS_ENABLE_PLACEHOLDER_REGEX_EXT(false);
        service.query(s, configs);
    }
    @Test
    public void pk1() throws Exception{
        ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
        Table tab = service.metadata().table(this.table);
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "int").setPrimary(true);
        tab.addColumn("code", "varchar(10)").setComment("编码");
        service.ddl().create(tab);
        tab = service.metadata().table(this.table);
        tab.getColumn("ID").setNewName("SID");
        service.ddl().alter(tab);
        tab = service.metadata().table(this.table);
        LinkedHashMap<String, Column> columns = tab.getColumns();
        for(Column column:columns.values()){
            System.out.println("column:"+column+" pk:"+column.isPrimaryKey());
        }
    }


    @Test
    public void schema() throws Exception{

        Table table = new Table("tb_"+System.currentTimeMillis());
        table.addColumn("ID","INT");
        service.ddl().create(table);

        table = service.metadata().table(new Schema("hr"), "hr_user");
        for (int i=0; i<10; i++){
            CacheProxy.clear();
            table = service.metadata().table(new Schema("hr"), "hr_user");
        }


    }
    @Test
    public void timez() throws Exception{
        Table tab = service.metadata().table(this.table);
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "int").setPrimary(true);
        tab.addColumn("code", "varchar(10)").setComment("编码");
        tab.addColumn("reg_time", "timestamp with time zone").setComment("注册时间");
        tab.addColumn("upt_time", "timestamp WITH ZONE");
        tab.addColumn("apr_time", "TIMESTAMP_WITH_ZONE");
        tab.addColumn("b", "BOOLEAN");
        tab.addColumn("c", "BOOL");
        tab.addColumn("c1", "float(1)");
        tab.addColumn("c2", "float(11,1)");
        tab.addColumn("c3", "float");
        service.ddl().create(tab);
    }
    @Test
    public void pk() throws Exception{
        Table table = service.metadata().table("c_test", false);
        if(null != table){
            service.ddl().drop(table);
        }
        //在列属性上设置主键
        table = new Table("c_test");
        table.setComment("测试");
        table.addColumn("ID", "INT").setPrimary(true).setComment("主键");
        table.addColumn("CODE","VARCHAR(20,10)").setPrimary(true);
        table.addColumn("NAME","VARCHAR(20)");
        table.addColumn("C","VARCHAR(20)");
        service.ddl().create(table);
        PrimaryKey pk = table.getPrimaryKey();
        service.ddl().drop(pk);


        //在表上单独设置主键
        service.ddl().drop(table);
        table = new Table("c_test");
        table.addColumn("ID", "INT");
        table.addColumn("CODE","VARCHAR(20)");
        table.addColumn("NAME","VARCHAR(20)");
        table.setPrimaryKey("ID","CODE");
        service.ddl().create(table);

        table = service.metadata().table("c_test");
        pk = table.getPrimaryKey();
        service.ddl().drop(pk);
        table = service.metadata().table("c_test");
        pk = new PrimaryKey().setName("pks");
        pk.addColumn("NAME");
        table.setPrimaryKey(pk);
        service.ddl().save(table);



        //创建主键对象
        service.ddl().drop(table);
        table = new Table("c_test");
        table.addColumn("ID", "INT");
        table.addColumn("CODE","VARCHAR(20)");
        table.addColumn("NAME","VARCHAR(20)");
        pk = new PrimaryKey().setName("pk_test");
        pk.addColumn("ID", "ASC", 1).addColumn("CODE", "DESC",0);
        //pk.setPosition("ID",2).setPosition("CODE",1);
        table.setPrimaryKey(pk);
        service.ddl().create(table);

        table = service.metadata().table("c_test");
        table.getPrimaryKey().delete();
        service.ddl().create(table);

        pk = new PrimaryKey();
        pk.addColumn("ID").addColumn("NAME").setName("pk");
        table.setPrimaryKey(pk);
        service.ddl().save(table);

    }
    @Test
    public void position() throws Exception{
        Table table = service.metadata().table(catalog, schema, this.table);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(this.table);
        table.addColumn("C2","INT").setPosition(2);
        table.addColumn("C1","INT").setPosition(1);
        table.addColumn("C3", "int"); //没有设置排最后
        // 1 2 3
        service.ddl().create(table);

        table = service.metadata().table(this.table);
        table.getColumn("C3").setPosition(0);  //0表示第1位
        service.ddl().save(table);
        table = service.metadata().table(this.table);
        LinkedHashMap<String, Column> columns = table.getColumns();
        for(Column c:columns.values()){
            System.out.println(c.getName());
        }
    }
    @Test
    public void maps(){
        PageNavi navi = new DefaultPageNavi();
        List<Map> maps = service.maps(table, navi);
        System.out.println(maps);
    }
    @Test
    public void rename() throws Exception{
        ddl();
        Table table = service.metadata().table(catalog, schema, this.table);
        Column column = table.getColumn("CODE");
        column.setNewName("CODE_NEW");
        //table.addColumn(column);
        service.ddl().save(table);

    }
    @Test
    public void autoIncrement() throws Exception{
        Table table = service.metadata().table("TAB_AUTOINCREMENT");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("TAB_AUTOINCREMENT");
        table.addColumn("ID1","BIGINT").setAutoIncrement(true);
        table.addColumn("ID2","BIGINT").setAutoIncrement(true);
        service.ddl().create(table);
        table = service.metadata().table("TAB_AUTOINCREMENT");
        Column id1 = table.getColumn("ID1");
        Column idS = table.getColumn("ID2");
    }
    @Test
    public void date(){
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigStore configs = new DefaultConfigStore();
        configs.and("join_date", DateUtil.format("yyyy-MM-dd"));
        DataSet set = service.querys("crm_user", configs);
        System.out.println(set);
    }

    @Test
    public void version() throws SQLException {
        String name = jdbc.getDataSource().getConnection().getMetaData().getDatabaseProductName();
        String version = jdbc.getDataSource().getConnection().getMetaData().getDatabaseProductVersion();
        String catalog = jdbc.getDataSource().getConnection().getCatalog();
        String schema = jdbc.getDataSource().getConnection().getSchema();
        log.warn("\nname:{}\nversion:{}\ncatalog:{}\nschema:{}",name, version, catalog, schema);
    }

    @Test
    public void info() {
        log.warn("\ntype:{}\ncatalog:{}\nschema:{}\ndatabase:{}\nproduct:{}\nversion:{}"
                ,service.metadata().type()
                ,service.metadata().catalog()
                ,service.metadata().schema()
                ,service.metadata().database()
                ,service.metadata().product()
                ,service.metadata().version()
        );
    }
    @Test
    public void page(){
        ConfigStore configs = new DefaultConfigStore();
        configs.setPageNavi(new DefaultPageNavi(2,3));
        DataSet set = service.querys("crm_user", configs);
        System.out.println(set);
    }

    @Test
    public void metadata(){
        LinkedHashMap<String,Column> columns = service.metadata("SELECT M.*, F.id as DV FROM CRM_USER AS M LEFT JOIN BS_ARRAY AS F ON M.ID = F.ID  WHERE M.ID = :ID ", true, true);
        for(Column column:columns.values()){
            System.out.println(BeanUtil.object2json(column));
        }
    }
    @Test
    public void test(){
        DataSet set =new DataSet();
        DataRow row = new DataRow();
        row.put("ID" ,"1");
        row.put("reg_time", new Date());
        set.add(row);
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigTable.IS_UPPER_KEY = false;
        //批量插入数据
        set.setOverride(true);
        service.save(100, "crm_user", set);

        set = service.querys("crm_user", "ID:>=1");
        System.out.println(set);

    }
    @Test
    public void type() throws Exception{ 
       Table tab = service.metadata().table(new Schema("public"), table, false);
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("id", "bigint").autoIncrement(true).primary(true);
        tab.addColumn("code","varchar(100)");
        tab.addColumn("age","bigint");
        tab.addColumn("name","varchar(100)");
        tab.addColumn("reg_time","datetime");

        service.ddl().create(tab);
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        List list = new ArrayList();
        Map<String,Object> map = new HashMap<>();
        map.put("ID", "12");
        map.put("age", "3");
        list.add(map);
        //map区分大小写 所以转换成DataSet 可以忽略或兼容 大小写 下划线 驼峰
        //service.insert(table, new DataSet(list));
        service.save(table, new DataSet(list));
        ConfigStore configs = new DefaultConfigStore();
        configs.ge("id","1");
        service.maps(table, configs,"REG_TIME:"+System.currentTimeMillis());
    }

    @Test
    public void array() throws Exception{
        Table tab = service.metadata().table("bs_array");
        if(null != tab) {
            service.ddl().drop(tab);
        }
        tab = new Table("bs_array");
        tab.addColumn("ID", "bigint", false, null).setComment("主键").autoIncrement(true).primary(true);
       // tab.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        //tab.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
      //  tab.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
      //  tab.addColumn("DATA_VERSION", "DECIMAL(10,2)", false, 1.1).setComment("数据版本");
        tab.addColumn("ARRAY_INT", "int[]"); //读取出来是_int
        tab.addColumn("ARRAY_INTS", "_int");
        tab.addColumn("ARRAY_CHAR", "varchar[]");
        tab.addColumn("ARRAY_CHARS", "_varchar");
        service.ddl().create(tab);

        tab = service.metadata().table("bs_array");
        LinkedHashMap<String, Column> cols = tab.getColumns();
        for(Column column:cols.values()){
            System.out.println(column.getName()+":"+column.getTypeName()+":"+column.isArray());
            System.out.println(column.getName()+":"+column.getTypeMetadata().getName());
        }
        DataRow row = new DataRow();
        int[] ints = {1,2,3};
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        row.put("ARRAY_INT", ints);
        //ConfigTable.IS_AUTO_SPLIT_ARRAY = false;
        //
        //insert前自动检测 数据类型
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        //直接设置数据类型
        row.setMetadata(new Column("ARRAY_INT").setArray(true));
        row.put("CODE", new ArrayList<>());
        row.put("NAME", new String[1]);
        row.put("REG_TIME", null);
        service.insert("bs_array", row);

        list.add(123);
        row.put("ARRAY_INT" ,list);
        List<String> names = new ArrayList<>();
        names.add("a");
        names.add("b");
        row.put("ARRAY_CHAR", names);
        service.save("bs_array", row);

        DataSet set = service.querys("bs_array", "1=any(ARRAY_INT)");
        set = service.querys("bs_array", new DefaultConfigStore().and("1=any(ARRAY_INT)"));

        ConfigStore config = new DefaultConfigStore();
        config.and("1=any(ARRAY_INT)").or("2=any(ARRAY_INT)");
        ConfigStore configs = new DefaultConfigStore();
        configs.and("ID>1").and(config);
        set = service.querys("public.bs_array", configs);
        System.out.println(set.size());
    }
    @Test
    public void column()throws Exception{

        LinkedHashMap<String, Column> cols = service.metadata().columns(false, null, new Schema("simple_crm"), table);
        //第二次应该从缓存中获取
        cols = service.metadata().columns(false, null, new Schema("simple_crm"), table);
        for(Column col:cols.values()){
            System.out.println(col.getSchema()+"."+col.getTableName(true)+"."+col.getName());
        }
        cols = service.metadata().columns(table);
        for(Column col:cols.values()){
            System.out.println(col.getSchema()+"."+col.getTableName(true)+"."+col.getName());
        }
    }
    @Test
    public void geometry() throws Exception{
        ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
        DataRow row = service.query("bs_geometry");
        //Geometry point = GeometryParser.parse(row.getBytes("WORK_LOCATION"));
        //Geometry line = GeometryParser.parse(row.getBytes("WORK_TRACE"));
        //System.out.println(point);
        System.out.println(row);
        Object point = row.get("WORK_LOCATION");
        System.out.println("WORK_LOCATION:"+point);
        Object line = row.get("WORK_TRACE");
        System.out.println("WORK_TRACE:"+line);
        Object polygon = row.get("WORK_AREA");
        System.out.println("WORK_AREA:"+polygon);
        Object polygons = row.get("WORK_AREAS");
        System.out.println("WORK_AREAS:"+polygons);
        System.out.println("WORK_POINTS:"+row.get("WORK_POINTS"));
        System.out.println("WORK_TRACES:"+row.get("WORK_TRACES"));
        System.out.println("WORK_AREAS:"+row.get("WORK_AREAS"));
        System.out.println("WORK_COL:"+row.get("WORK_COL"));
    }
    @Test
    public void ddl() throws Exception{
        ConfigTable.IS_PRINT_EXCEPTION_STACK_TRACE = true;
        ConfigTable.IS_THROW_SQL_UPDATE_EXCEPTION = true; //遇到SQL异常直接抛出
        //检测表结构
        Table table = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != table){
            service.ddl().drop(table);
        }
        //也可以直接删除(需要数据库支持 IF EXISTS)
        service.ddl().drop(new Table(catalog, schema, this.table));

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNull(table);

        //定义表结构
        table = new Table<>(catalog, schema, this.table).setComment("ge备注");

        //添加列
        //自增长列 如果要适配多种数据库 autoIncrement 有必须的话可以设置起始值与增量 autoIncrement(int seed, int step)
        table.addColumn("ID", "bigint", false, null).setComment("主键").autoIncrement(true).primary(true);
        table.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        table.addColumn("JOIN_DATE", "DATE").setComment("日期");
        table.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        //默认当前时间 如果要适配多种数据库 用 SQL_BUILD_IN_VALUE.CURRENT_DATETIME
        table.addColumn("REG_TIME", "datetime").setComment("注册时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        table.addColumn("DATA_VERSION", "DECIMAL(10,2)", false, 1.1).setComment("数据版本");

        Index index = new Index();
        index.addColumn("CODE").addColumn("NAME");
        table.add(index);
        //创建表
        service.ddl().create(table);

        //再查询一次
        table = service.metadata().table(catalog, schema, this.table);
        Assertions.assertNotNull(table);
    }


    @Test
    public void sequence(){
        LinkedHashMap<String,Sequence> sequences = service.metadata().sequences();
        for(Sequence sequence:sequences.values()){
            System.out.println(BeanUtil.object2json(sequence));
        }
    }

    @Test
    public void exists(){
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        DataRow row = new DataRow();
        row.put("ID", 1L);
        service.exists("CRM_USER",row);
    }
    @Test
    public void databases() throws SQLException {
        LinkedHashMap<String, Database> databases = service.metadata().databases();
        for(Database database:databases.values()){
            log.warn("数据库:{}", database.getName());
        }
    }
    @Test
    public void execute(){
        DataRow row = new DataRow();
        row.put("name","zh"+System.currentTimeMillis());
        service.insert(table, row);

        ConfigStore configs = new DefaultConfigStore().execute(false);
        DataSet set = service.querys(table, configs);
        System.out.println(set);//这一步应该输出空集合

        List<Run> runs = configs.runs();
        for (Run run:runs){
             System.out.println("无占位符 sql:"+run.getFinalQuery(false));
             System.out.println("有占位符 sql:"+run.getFinalQuery());
             System.out.println("占位values:"+run.getValues());
        }
    }
    @Test
    public   void tables() throws Exception{
        System.out.println("-------------------------------- start tables  --------------------------------------------");
        List<Table> list = service.metadata().tables(true);
        for(Table table:list){
            System.out.println(table);
        }
        LinkedHashMap<String,Table> tables = service.metadata().tables();
        for(String key:tables.keySet()){
            Table table = tables.get(key);
            log.warn("table:"+table.getName());
            log.warn("comment:"+table.getComment());
        }

        //当前schema中没有的表 默认查不到
        Table table = service.metadata().table("art_comment");
        if(null != table) {
            System.out.println(table.getCatalog() + ":" + table.getSchema() + ":" + table.getName());
        }
        //当前schema中没有的表 greedy=rue 可以查到其他schema中的表
        table = service.metadata().table(true,"art_comment");
        if(null != table) {
            System.out.println(table.getCatalog() + ":" + table.getSchema() + ":" + table.getName());
        }

        System.out.println("-------------------------------- end tables  ----------------------------------------------");
    }
    @Test
    public void dml() throws Exception{
        DataSet set = new DataSet();
        for(int i=1; i<10; i++){
            DataRow row = new DataRow();
            //只插入NAME  ID自动生成 REG_TIME 默认当时时间
            row.put("NAME", "N"+i);
            set.add(row);
        }
        long qty = service.insert(table, set);
        log.warn(LogUtil.format("[批量插入][影响行数:{}][生成主键:{}]", 36), qty, set.getStrings("ID"));
        Assertions.assertEquals(qty , 9);

        DataRow row = new DataRow();
        row.put("NAME", "N");
        //当前时间，如果要适配多种数据库环境尽量用SQL_BUILD_IN_VALUE,如果数据库明确可以写以根据不同数据库写成: row.put("REG_TIME","${now()}"); sysdate,getdate()等等
        row.put("REG_TIME", JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        qty = service.insert(table, row);
        log.warn(LogUtil.format("[单行插入][影响行数:{}][生成主键:{}]", 36), qty, row.getId());
        Assertions.assertEquals(qty , 1);
        Assertions.assertNotNull(row.getId());

        //查询全部数据
        set = service.querys(table);
        log.warn(LogUtil.format("[query result][查询数量:{}]", 36), set.size());
        log.warn("[多行查询数据]:{}",set.toJSON());
        Assertions.assertEquals(set.size() , 10);

        //只查一行
        row = service.query(table);
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);
        Assertions.assertEquals(row.getId(), "1");

        //查最后一行
        row = service.query(table, "ORDER BY ID DESC");
        log.warn("[单行查询数据]:{}",row.toJSON());
        Assertions.assertNotNull(row);
        Assertions.assertEquals(row.getInt("ID",10), 10);

        //更新
        //put覆盖了Map.put返回Object
        row.put("NAME", "SAVE NAME");

        //set由DataRow声明实现返回DataRow可以链式操作
        row.set("CODE", "SAVE CODE").set("DATA_VERSION", 1.2);

        //save根据是否有主键来判断insert | update
        //可以指定SAVE哪一列
        service.save(row, "NAME");
        service.save(row);
        row.put("NAME", "UPDATE NAME");

        /*
         * 注意这里的page一般不手工创建，而是通过AnylineController中的condition自动构造
         * service.querys("CRM_USER", condition(true, "ID:id","NAME:%name%", TYPE_CODE:[type]), "AGE:>=age");
         * true:表示分页 或者提供int 表示每页多少行
         * ID:表示数据表中的列
         * id:表示http提交的参数名
         * [type]:表示数组
         * */

        //分页查询
        //每页3行,当前第2页(下标从1开始)
        PageNavi page = new DefaultPageNavi(2, 3);

        //无论是否分页 都返回相同结构的DataSet
        set = service.querys(table, page);
        log.warn(LogUtil.format("[分页查询][共{}行 第{}/{}页]", 36), page.getTotalRow(), page.getCurPage(), page.getTotalPage());
        log.warn(set.toJSON());
        Assertions.assertEquals(page.getTotalPage() , 4);
        Assertions.assertEquals(page.getTotalRow() , 10);


        //模糊查询
        set = service.querys("CRM_USER", "NAME:%N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("CRM_USER", "NAME:%N");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());
        set = service.querys("CRM_USER", "NAME:N%");
        log.warn(LogUtil.format("[模糊查询][result:{}]", 36), set.toJSON());

        //其他条件查询
        //in
        List<Integer> in = new ArrayList<>();
        in.add(1);
        in.add(2);
        in.add(3);
        ConfigStore condition = new DefaultConfigStore();
        condition.ands("ID", in);

        //not in
        condition.and(Compare.NOT_IN, "NAME", "N1");
        List<Integer> notin = new ArrayList<>();
        notin.add(10);
        notin.add(20);
        notin.add(30);
        condition.and(Compare.NOT_IN, "ID", notin);

        //between
        List<Integer> between = new ArrayList<>();
        between.add(1);
        between.add(10);
        condition.and(Compare.BETWEEN, "ID", between);

        // >=
        condition.and(Compare.GREAT_EQUAL, "ID", 1);

        //前缀
        condition.and(Compare.LIKE_PREFIX, "NAME", "N");

        set = service.querys("CRM_USER", condition);
        log.warn(LogUtil.format("[后台构建查询条件][result:{}]", 36), set.toJSON());
        Assertions.assertEquals(set.size() , 2);

        qty = service.count(table);
        log.warn(LogUtil.format("[总数统计][count:{}]", 36), qty);
        Assertions.assertEquals(qty , 10);

        //根据默认主键ID更新
        row.put("CODE","1001");
        //默认情况下 更新过的列 会参与UPDATE
        qty = service.update(row);
        log.warn(LogUtil.format("[根据主键更新内容有变化的化][count:{}]", 36), qty);


        //根据临时主键更新,注意这里更改了主键后ID就成了非主键，但未显式指定更新ID的情况下,ID不参与UPDATE
        row.setPrimaryKey("NAME");
        qty = service.update(row);
        log.warn(LogUtil.format("[根据临时主键更新][count:{}]", 36), qty);

        //显示指定更新列的情况下才会更新主键与默认主键
        qty = service.update(row,"NAME","CODE","ID");
        log.warn(LogUtil.format("[更新指定列][count:{}]", 36), qty);

        //根据条件更新
        ConfigStore store = new DefaultConfigStore();
        store.and(Compare.GREAT, "ID", 1)
                
                .and(" CODE > '1'")
                .and("NAME IS NOT NULL");
        qty = service.update(row, store);
        log.warn(LogUtil.format("[根据条件更新][count:{}]", 36), qty);


        qty = service.delete(set);
        log.warn("[根据ID删除集合][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());

        //根据主键删除
        qty = service.delete(row);
        log.warn("[根据ID删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, 1);

        set = service.querys(table, "CODE:1001");
        qty = service.delete(table, "CODE", "1001");
        log.warn("[根据条件删除][删除数量:{}]", qty);
        Assertions.assertEquals(qty, set.size());
    }

    /**
     * 继承
     * @throws  Exception Exception
     */
    @Test
    public void inherits() throws Exception{
        Table c = service.metadata().table("tab_c1", false);
        if(null != c){
            service.ddl().drop(c);
        }
        Table p = service.metadata().table("tab_parent", false);
        if(null != p){
            service.ddl().drop(p);
        }

        //父表
        p = new Table("tab_parent");
        //p.addColumn("id","int").autoIncrement(true).primary(true);
        p.addColumn("code","varchar(10)");
        service.ddl().create(p);

        //子表1
        c = new Table("tab_c1");
        c.setInherit(p);
        c.addColumn("name","varchar(10)");
        service.ddl().create(c);
        //子表2 不添加新列
        c = new Table("tab_c2");
        c.setInherit(p);
        service.ddl().create(c);

        //在父表添加一列
        Column column = new Column(p, "code_p", "int");
        service.ddl().add(column);
        System.out.println("父表columns:"+service.columns(p));
        System.out.println("子表columns:"+service.columns(c));
        //子表添加一列
        column = new Column(c, "code_c", "int");
        service.ddl().add(column);
        System.out.println("父表columns:"+service.columns(p));
        System.out.println("子表columns:"+service.columns(c));

        p.addColumn("code_p2","int");
        service.ddl().save(p);


        System.out.println("父表columns:"+service.columns(p));
        System.out.println("子表columns:"+service.columns(c));

        c.addColumn("code_c2","int");
        service.ddl().save(c);
        System.out.println("父表columns:"+service.columns(p));
        System.out.println("子表columns:"+service.columns(c));

    }

    @Test
    public void batch() throws Exception{
        Table tab = service.metadata().table(catalog, schema, this.table);
        //如果存在则删除
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table(table);
        tab.addColumn("ID", "bigint", false, null).setComment("主键").autoIncrement(true).primary(true);
        tab.addColumn("CODE", "VARCHAR(50)").setComment("编号");
        tab.addColumn("NAME", "VARCHAR(50)").setComment("名称");
        service.ddl().save(tab);
        long id = System.currentTimeMillis();
        DataSet set = new DataSet();
        //插入10行
        for(int i=0; i<10; i++){
            DataRow row = new DataRow();
            row.put("ID", id + i);
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "N_"+i);
            set.add(row);
        }
        service.insert(table ,set);
        set = new DataSet();
        for(int i=0; i<15; i++){
            DataRow row = new DataRow();
            if(i<10) {
                row.put("ID", id + i);
            }
            row.put("CODE", "C_"+id + i);
            row.put("NAME", "U_N"+i);
            set.add(row);
        }
        //save 15行，其中10行update 5行insert
        service.save(100,table ,set);
    }

    /**
     * 分区表
     */

    @Test
    public void partition_range() throws Exception{
        //根据范围分区
        Table table = service.metadata().table("log_master", false);
        if(null != table){
            service.ddl().drop(table);
        }
        MasterTable master = new MasterTable("log_master");
        master.addColumn("ID", "INT");
        master.addColumn("NAME", "VARCHAR(10)").setComment("姓名");

        Partition partition = new Partition();
        partition.setType(Partition.TYPE.RANGE);
        partition.addColumn("ID");
        master.setPartition(partition);
        //或者
        master.partitionBy(Partition.TYPE.RANGE, "ID");
        service.ddl().create(master);

        PartitionTable log1 = new PartitionTable("LOG1");
        log1.setMaster(master);
        log1.setPartition(new Partition(Partition.TYPE.RANGE).setMin(0).setMax(99));
        service.ddl().create(log1);
        PartitionTable log2 = new PartitionTable("LOG2");
        log2.setMaster(master);
        //也可以不设置type共用主表的type
        log2.setPartition(new Partition().setMin(100).setMax(199));
        service.ddl().create(log2);
        DataRow log = new DataRow();
        log.put("ID", 111);
        log.put("NAME","z");
        service.insert("log_master", log);

        //从主表或相就的分区表中可以查到
        service.querys("log_master");
        service.querys("LOG2");
    }
    @Test
    public void partition_range_date() throws Exception{
        //根据时间范围分区
        Table table = service.metadata().table("log_master", false);
        if(null != table){
            service.ddl().drop(table);
        }
        MasterTable master = new MasterTable("log_master");
        master.addColumn("ID", "INT");
        master.addColumn("YMD", "DATE").setComment("日期");
        master.addColumn("NAME", "VARCHAR(10)").setComment("姓名");
        //设置主表分区依据的列
        master.partitionBy(Partition.TYPE.RANGE, "YMD");
        service.ddl().create(master);

        PartitionTable log1 = new PartitionTable("LOG1");
        log1.setMaster(master);
        //设置分区范围
        log1.setPartition(new Partition(Partition.TYPE.RANGE).setMin("2020-01-01").setMax("2020-01-31"));
        service.ddl().create(log1);
        PartitionTable log2 = new PartitionTable("LOG2");
        log2.setMaster(master);
        //也可以不设置type共用主表的type
        log2.setPartition(new Partition().setMin("2020-02-01").setMax("2020-05-31"));
        service.ddl().create(log2);
        DataRow log = new DataRow();
        log.put("ID", 111);
        log.put("YMD",  LocalDate.of(2020,3,1));
        log.put("NAME","z");
        service.insert("log_master", log);

        //从主表或相就的分区表中可以查到
        service.querys("log_master");
        service.querys("LOG2");
    }
    @Test
    public void partition_list() throws Exception{
        //根据部门编号 分区
        Table table = service.metadata().table("user_master", false);
        if(null != table){
            service.ddl().drop(table);
        }
        MasterTable master = new MasterTable("user_master");
        master.addColumn("ID", "INT");
        master.addColumn("NAME", "VARCHAR(10)").setComment("姓名");
        master.addColumn("DEPT_CODE", "VARCHAR(10)").setComment("部门ID");

        //或者
        master.partitionBy(Partition.TYPE.LIST, "DEPT_CODE");
        service.ddl().create(master);

        PartitionTable sd = new PartitionTable("USER_SD_PP");
        sd.setMaster(master);
        sd.setPartition(new Partition(Partition.TYPE.LIST).addValues("SD", "PP"));
        service.ddl().create(sd);
        PartitionTable fi = new PartitionTable("USER_FI_CO");
        fi.setMaster(master);
        fi.setPartition(new Partition(Partition.TYPE.LIST).addValues("FI", "CO"));
        service.ddl().create(fi);

        DataRow user = new DataRow();
        user.put("DEPT_CODE", "FI");//部门编号必须是子表 分区依据中出现的 并 操持大小写一致
        user.put("NAME","ZH_FI");
        //通过主表插入
        service.insert("user_master", user);


        //从主表或相就的分区表中可以查到
        service.querys("user_master");
        service.querys("USER_FI_CO");
    }
    @Test
    public void partition_hash() throws Exception{
        Table table = service.metadata().table("user_master", false);
        if(null != table){
            service.ddl().drop(table);
        }
        MasterTable master = new MasterTable("user_master");
        master.addColumn("ID", "INT");
        master.addColumn("NAME", "VARCHAR(10)").setComment("姓名");
        master.addColumn("DEPT_CODE", "int").setComment("部门ID");
        Partition partition = new Partition();
        partition.setType(Partition.TYPE.HASH).setColumns("ID");
        master.setPartition(partition);
        service.ddl().create(master);

        PartitionTable u1 = new PartitionTable("U1");
        u1.setMaster(master);
        u1.setPartition(new Partition().setHash(3,0));
        service.ddl().create(u1);
        PartitionTable u2 = new PartitionTable("U2");
        u2.setMaster(master);
        u2.setPartition(new Partition().setHash(3,1));
        service.ddl().create(u2);
        PartitionTable u3 = new PartitionTable("U3");
        u3.setMaster(master);
        u3.setPartition(new Partition().setHash(3,2));
        service.ddl().create(u3);

        DataRow user = new DataRow();
        user.put("DEPT_CODE",5);//部门编号必须是子表 分区依据中出现的 并 操持大小写一致
        user.put("NAME","ZH_FI");
        //通过主表插入
        service.insert("user_master", user);


        //从主表或相就的分区表中可以查到
        service.querys("user_master");
        service.querys("u3");
    }

    @Test
    public void update(){
        DataRow row = new DataRow();
        row.put("ID", "1");
        row.put("CODE", "2");
        service.update("CRM_USER", row
            , new DefaultConfigStore()
                .and("NAME","3") //有了configs条件会忽略主键条件
                .columns("ID","CODE"));//默认不更新主键除非显示指定
    }
    @Test
    public void updateEmptyCondition(){
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        DataSet set = service.querys("simple.hr.hr_user", 0, 10);
        System.out.println(set.getTable());
        set.put("code","1");
        service.update(set);
    }
    @Test
    public void help() throws Exception{
        Connection con = jdbc.getDataSource().getConnection();
        System.out.println("\n--------------[metadata]------------------------");
        System.out.println("catalog:"+con.getCatalog());
        System.out.println("schema:"+con.getSchema());
        ResultSet set = con.getMetaData().getTables(null, null, table, "TABLE".split(","));
        ResultSetMetaData md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[table metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",20) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }
        set = jdbc.getDataSource().getConnection().getMetaData().getColumns(null, null, null, null);
        md = set.getMetaData();
        if (set.next()) {
            System.out.println("\n--------------[column metadata]------------------------");
            for (int i = 1; i <= md.getColumnCount(); i++) {
                String column = md.getColumnName(i);
                System.out.print(BasicUtil.fillRChar(column, " ",37) + " = ");
                Object value = set.getObject(i);
                System.out.println(value);
            }
        }

    }
}
